<?php class Mod_listclinic extends CI_Model {
	
	function __construct()
	    {
		parent::__construct();
	    }
            /*pour retourner le nombre des cliniques valider*/
            public function record_count_getAllClinicFetch() {
            $sql = "SELECT * FROM clinique INNER JOIN compte_clinique ON clinique.ID_COMPTE_CLI = compte_clinique.ID_COMPTE_CLI WHERE compte_clinique.ETAT=1";			
                    $Q = $this->db->query($sql);
                    $i=0;		
                    if ($Q->num_rows()>0){
                            foreach ($Q->result_array() as $row){
                            $data[]=$row;
                            $i++;
                            }
                    }
                    $Q->free_result();
                    return $i;
        }
        
        public function record_count_getClinicFetch($pays,$ville,$keywords,$language) {
                    $sql = "SELECT * FROM clinique cl, compte_clinique c_cl, pays p, ville v "
                    . "WHERE clinique.ID_COMPTE_CLI = compte_clinique.ID_COMPTE_CLI "
                    . "AND clinique.ID_PAYS = pays.ID_PAYS"
                    . "AND clinique.ID_VILLE = ville.ID_VILLE"
                    . "AND compte_clinique.ETAT=1 "
                    . "And (pays.pays_fr ='$pays' OR  pays.pays_en ='$pays') "
                    . "And (ville.nom_ville ='$ville' OR  ville.nom_ville ='$ville') ";
                    
                    // to be corrected ....
                    $sql = "SELECT * FROM clinique as c, compte_clinique as c_cl, pays as p, ville as v WHERE c.ID_COMPTE_CLI = c_cl.ID_COMPTE_CLI AND c.ID_PAYS = p.ID_PAYS AND c.ID_VILLE = v.ID_VILLE AND c_cl.ETAT=1 And (p.pays_fr ='Cambodge' OR p.pays_en ='Cambodge') And (v.nom_ville ='ville1' OR v.nom_ville ='ville1')";
                    //. "And $keywords "
                    //. "And $language ";
                    
                    echo "SQL: $sql";
                    $Q = $this->db->query($sql);
                    $i=0;		
                    if ($Q->num_rows()>0){
                            foreach ($Q->result_array() as $row){
                            $data[]=$row;
                            $i++;
                            }
                    }
                    $Q->free_result();
                    return $i;
        }
        
        /*pour retourner la liste des cliniques validées*/
            function getAllClinicFetch($limit, $start){
		$sql = "SELECT * FROM clinique INNER JOIN compte_clinique ON clinique.ID_COMPTE_CLI = compte_clinique.ID_COMPTE_CLI WHERE compte_clinique.ETAT=1 LIMIT $start , $limit";			
		$data=array();
                $Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
        
        /*pour retourner la liste des cliniques validées*/
            function getClinicFetch($pays,$ville,$keywords,$language,$limit, $start){
		$sql = "SELECT * FROM clinique "
                    . "INNER JOIN compte_clinique "
                    . "ON clinique.ID_COMPTE_CLI = compte_clinique.ID_COMPTE_CLI "
                    . "INNER JOIN pays "
                    . "ON clinique.ID_PAYS = pays.ID_PAYS"
                    . "INNER JOIN ville "
                    . "ON clinique.ID_VILLE = ville.ID_VILLE"
                    . "WHERE compte_clinique.ETAT=1 "
                    . "And (pays.pays_fr ='$pays' OR  pays.pays_en ='$pays') "
                    . "And (ville.nom_ville ='$ville' OR  ville.nom_ville ='$ville') "
                        . "LIMIT $start , $limit";
                $data=array();
                $Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
        
        /*DEBUT PAYS*/
        /*retourner identifiant pays par son nom*/
        function getIdPaysByName($pays){
            $sql = "SELECT ID_PAYS FROM pays WHERE PAYS_FR='$pays'";			
		$id_pays=0;
                $Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$id_pays=$row['ID_PAYS'];
			}
		}
		$Q->free_result();
		return $id_pays;
            
        }
        /*retourner nombre clinique par pays*/
        function getNbClinicByPays($id_pays){
            $sql = "SELECT * FROM clinique WHERE ID_PAYS='$id_pays'";			
		$nb_cli=0;
                $Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$nb_cli++;
			}
		}
		$Q->free_result();
		return $nb_cli++;
        }
        /*retourner liste clinique par pays*/
        function getAllClinicByPays($id_pays,$limit, $start){
		$sql = "SELECT * FROM clinique WHERE ID_PAYS='$id_pays' LIMIT $start,$limit";			
		$data=array();
                $Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
        /*retourner Img Header du clinique*/
        function getClinicLogo($id_clinic){
		$sql = "SELECT * FROM logo WHERE ID_CLINIC = $id_clinic ";			
		$data=array();
                $Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
        /*retourner Img Header du clinique*/
        function getClinicImgHead($id_clinic){
		$sql = "SELECT * FROM photo WHERE ID_CLI = $id_clinic and header = 1";			
		$data=array();
                $Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
        /*FIN PAYS*/
        /*VILLE*/
        /*retourner identifiant ville par son nom*/
        function getIdVilleByName($ville){
            $sql = "SELECT ID_VILLE FROM ville WHERE NOM_VILLE='$ville'";			
		$id_ville=0;
                $Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$id_ville=$row['ID_VILLE'];
			}
		}
		$Q->free_result();
		return $id_ville;
            
        }
        /*retourner le nombre de clinique par ville*/
        function getNbClinicByVille($id_ville){
            $sql = "SELECT * FROM clinique WHERE ID_VILLE='$id_ville'";			
		$nb_cli=0;
                $Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$nb_cli++;
			}
		}
		$Q->free_result();
		return $nb_cli++;
        }
        /*retourner liste clinique par ville*/
        function getAllClinicByVille($id_ville,$limit, $start){
		$sql = "SELECT * FROM clinique WHERE ID_VILLE='$id_ville'";		/*LIMIT $start,$limit*/	
		$data=array();
                $Q = $this->db->query($sql);
		if ($Q->num_rows()>0){
			foreach ($Q->result_array() as $row){
				$data[]=$row;
			}
		}
		$Q->free_result();
		return $data;
	}
}